with dwd_tab_send_arrival_base_dt as(
select
     scan.waybill_no            as waybill_no          --运单号
    ,scan.scan_time             as scan_time           --扫描时间
    ,scan.input_time            as input_time          --录入时间
    ,dim.code                   as scan_site_code      --扫描网点编码
    ,dim.name                   as scan_site_name      --扫描网点名称
    ,scan.next_station_code     as next_station_code   --上下一站
    ,next_dim.network_type      as next_network_type   --下下一站类型
    ,scan.transfer_code         as transfer_code       --任务号
    ,scan.scan_type             as scan_type           --扫描类型
    ,dim.network_type           as network_type        --当前站类型
from (
    select
         send.waybill_no                                    as waybill_no         --运单号
        ,nvl(send.scan_time        ,load.scan_time        ) as scan_time          --扫描时间
        ,nvl(send.input_time       ,load.input_time       ) as input_time         --录入时间
        ,nvl(send.scan_site_code   ,load.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(send.scan_site_name   ,load.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(send.next_station_code,load.next_station_code) as next_station_code  --转单号
        ,nvl(send.transfer_code    ,load.transfer_code    ) as transfer_code      --转单号
        ,send.scan_type                                     as scan_type          --扫描类型
    from(
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,input_site           as scan_site_name     --扫描网点名称
            ,next_station_code    as next_station_code  --上一站
            ,shipment_no          as transfer_code      --任务号
            ,'send'               as scan_type          --扫描类型
       from jms_dwd.dwd_tab_barscan_send_base_hi  --发件
        where dt between date_add('{{ execution_date| cst_ds }}', -2) and '{{ execution_date | cst_hour }}'
          and scan_time between concat(date_add('{{ execution_date| cst_ds }}', -2),' 00:00:00') and '{{ execution_date | hour_add(1) | cst_ds_sfm }}'
          and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) send
    left join (
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,next_station_code    as next_station_code  --上一站
            ,shipment_no          as transfer_code      --任务号
            ,'load'               as scan_type
        from jms_dwd.dwd_tab_barscan_loading_base_hi --装车
        where dt between date_add('{{ execution_date| cst_ds }}', -2) and '{{ execution_date | cst_hour }}'
         and  scan_time between concat(date_add('{{ execution_date| cst_ds }}', -2),' 00:00:00') and '{{ execution_date | hour_add(1) | cst_ds_sfm }}'
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) load on send.waybill_no = load.waybill_no
          and send.scan_site_code = load.scan_site_code
          and send.scan_time = load.scan_time
    union all
    select
         arrival.waybill_no                                      as waybill_no         --运单号
        ,nvl(arrival.scan_time        ,unload.scan_time        ) as scan_time          --扫描时间
        ,nvl(arrival.input_time       ,unload.input_time       ) as input_time         --录入时间
        ,nvl(arrival.scan_site_code   ,unload.scan_site_code   ) as scan_site_code     --扫描网点编码
        ,nvl(arrival.scan_site_name   ,unload.scan_site_name   ) as scan_site_name     --扫描网点名称
        ,nvl(arrival.next_station_code,unload.next_station_code) as next_station_code  --转单号
        ,nvl(arrival.transfer_code    ,unload.transfer_code    ) as transfer_code      --转单号
        ,arrival.scan_type                                       as scan_type          --扫描类型
    from(
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --上一站
            ,transfer_code        as transfer_code      --任务号
            ,'arrival'            as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_arrival_base_hi arrival --到件
        where dt between date_add('{{ execution_date| cst_ds }}', -2) and '{{ execution_date | cst_hour }}'
          and scan_time between concat(date_add('{{ execution_date| cst_ds }}', -2),' 00:00:00') and '{{ execution_date | hour_add(1) | cst_ds_sfm }}'
          and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) arrival
    left join (
        select
             waybill_no           as waybill_no         --运单号
            ,scan_time            as scan_time          --扫描时间
            ,input_time           as input_time         --录入时间
            ,scan_site_code       as scan_site_code     --扫描网点编码
            ,scan_site            as scan_site_name     --扫描网点名称
            ,pre_station_code     as next_station_code  --上一站
            ,shipment_no          as transfer_code      --任务号
            ,'unload'             as scan_type          --扫描类型
        from jms_dwd.dwd_tab_barscan_unloading_base_hi --卸车
        where dt between date_add('{{ execution_date| cst_ds }}', -2) and '{{ execution_date | cst_hour }}'
         and scan_time between concat(date_add('{{ execution_date| cst_ds }}', -2),' 00:00:00') and '{{ execution_date | hour_add(1) | cst_ds_sfm }}'
         and !(length(waybill_no) = 13 and waybill_no like 'B%')
    ) unload on arrival.waybill_no = unload.waybill_no
            and arrival.scan_site_code = unload.scan_site_code
            and arrival.scan_time = unload.scan_time
) scan
left join (
    select
         code
        ,name
        ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表 当前站
) dim on scan.scan_site_code = dim.code
left join (
    select
         code
        ,name
        ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表 下一站
) next_dim on scan.next_station_code = next_dim.code
),




dwd_tab_wide_arrival_base_dt as (
    select *
    from(
        select
             waybill_no          --运单号
            ,scan_time           --扫描时间
            ,input_time          --录入时间
            ,scan_site_code      --扫描网点编码
            ,scan_site_name      --扫描网点名称
            ,next_station_code   --上下一站
            ,transfer_code       --转单号
            ,scan_type           --扫描类型
            ,network_type        --当前站类型
            ,next_network_type   --下一站类型
            ,row_number() over(partition by waybill_no,network_type order by scan_time asc) as rn --按照每个站点类型的最早时间排序，取出始发网点、集散、中心
        from (
            select
                 waybill_no         --运单号
                ,scan_time          --扫描时间
                ,input_time         --录入时间
                ,scan_site_code     --扫描网点编码
                ,scan_site_name     --扫描网点名称
                ,next_station_code  --转单号
                ,transfer_code      --转单号
                ,scan_type          --扫描类型
                ,network_type        --当前站类型
                ,next_network_type   --下一站类型
                ,row_number() over(partition by waybill_no,scan_site_code order by scan_time asc) as rk --按照每个运单站点的最早时间排序
            from dwd_tab_send_arrival_base_dt
            where scan_type = 'arrival'
        ) scan where scan.rk = 1
    ) a where a.rn = 1
),




dwd_tab_wide_send_base_dt as (
    select *
    from(
        select
             waybill_no          --运单号
            ,scan_time           --扫描时间
            ,input_time          --录入时间
            ,scan_site_code      --扫描网点编码
            ,scan_site_name      --扫描网点名称
            ,next_station_code   --上下一站
            ,transfer_code       --转单号
            ,scan_type           --扫描类型
            ,network_type        --当前站类型
            ,next_network_type   --下一站类型
            ,row_number() over(partition by waybill_no,network_type order by scan_time asc) as rn --按照每个站点类型取出始发网点、集散、中心
        from (
            select
                 send.waybill_no         --运单号
                ,send.scan_time          --扫描时间
                ,send.input_time         --录入时间
                ,send.scan_site_code     --扫描网点编码
                ,send.scan_site_name     --扫描网点名称
                ,send.next_station_code  --转单号
                ,send.transfer_code      --转单号
                ,send.scan_type          --扫描类型
                ,send.network_type        --当前站类型
                ,send.next_network_type   --下一站类型
                ,row_number() over(partition by send.waybill_no,send.scan_site_code order by send.scan_time desc) as rk --按照每个运单站点的最晚时间排序
            from dwd_tab_send_arrival_base_dt send
            left join (
                select
                   waybill_no
                  ,max(case when network_type = 4 then scan_time end) as scan_time
                from dwd_tab_wide_arrival_base_dt
                group by waybill_no
            ) arr on send.waybill_no = arr.waybill_no
            where send.scan_type = 'send'
              and send.scan_time < nvl(arr.scan_time,'2099-99-99 00:00:00')
        ) scan where scan.rk = 1
    ) a where a.rn = 1
),


dwd_tab_wide_base_dt as (
select
    waybill_no          --运单号
   ,scan_time           --扫描时间
   ,input_time          --录入时间
   ,scan_site_code      --扫描网点编码
   ,scan_site_name      --扫描网点名称
   ,next_station_code   --上下一站
   ,transfer_code       --转单号
   ,scan_type           --扫描类型
   ,network_type        --当前站类型
   ,next_network_type   --下一站类型
from dwd_tab_wide_arrival_base_dt
union all
select
    waybill_no          --运单号
   ,scan_time           --扫描时间
   ,input_time          --录入时间
   ,scan_site_code      --扫描网点编码
   ,scan_site_name      --扫描网点名称
   ,next_station_code   --上下一站
   ,transfer_code       --转单号
   ,scan_type           --扫描类型
   ,network_type        --当前站类型
   ,next_network_type   --下一站类型
from dwd_tab_wide_send_base_dt
),


---补下一站逻辑
dwd_tab_send_next_base_dt as (
   select *
   from (
       select
             *,row_number() over(partition by waybill_no,next_station_code order by scan_time desc) as rk --按照最晚取，
       from dwd_tab_wide_base_dt --装车发件
       where scan_type = 'send'
       and next_station_code is not null
   ) a where a.rk = 1
)



---------------------------第一段：拿出T-2天到当前时间的到发件数据取出始发网点、始发集散、始发中心
insert overwrite table jms_tmp.dwd_tab_barscan_base_dt_T2
select
    wide.waybill_no        --运单号
   ,wide.scan_time         --扫描时间
   ,wide.input_time        --录入时间
   ,wide.scan_site_code    --扫描网点编码
   ,wide.scan_site_name    --扫描网点名称
   ,nvl(wide.next_station_code,send_next.scan_site_code) --转单号
   ,wide.transfer_code     --转单号
   ,wide.network_type
   ,wide.next_network_type
   ,wide.scan_type         --扫描类型
   ,send_center.first_center_send_time
from dwd_tab_wide_base_dt wide --发件
left join dwd_tab_send_next_base_dt send_next
on wide.waybill_no = send_next.waybill_no --关联发件取发件当前站 = 到件上一站
and wide.scan_site_code = send_next.next_station_code
and wide.scan_time > send_next.scan_time
and wide.scan_type = 'arrival'
left join (
    select
         waybill_no
        ,min(scan_time) as first_center_send_time
    from dwd_tab_send_arrival_base_dt
    where scan_type= 'send'
    and network_type = 4
    group by waybill_no
) send_center on wide.waybill_no = send_center.waybill_no
;




insert overwrite table jms_dm.dm_tab_barscan_arrival_send_base_hi partition (dt = '{{ execution_date | cst_hour }}')
select
     base.waybill_no          as waybill_no              --运单号
    ,base.actual_send_time    as actual_send_time        --网点实际发件时间
    ,base.first_network_code  as first_network_code      --始发网点编码
    ,base.first_nodal_name    as first_nodal_name        --始发集散名称
    ,base.first_nodal_code    as first_nodal_code        --始发集散编码
    ,base.first_center_code   as first_center_code       --始发中心编码
    ,base.first_center_name   as first_center_name       --始发中心名称
    ,base.first_center_code   as actual_center_code      --实际交件中心编码
    ,base.first_center_name   as actual_center_name      --实际交件中心名称
    ,base.first_center_time   as actual_center_time      --实际交件中心扫描时间
    ,case when branch.arrival_center_arrival_time is not null
          then least(nvl(branch.arrival_center_arrival_time,'2099-12-31 00:00:00') --到车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          when branch.send_center_arrival_time is not null
          then least(nvl(branch.send_center_arrival_time   ,'2099-12-31 00:00:00')--发车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          else base.first_center_time end as actual_center_arr_time
    ,base.first_nodal_code   as actual_collect_code         --实际交件集散点编码
    ,base.first_nodal_name   as actual_collect_name         --实际交件集散点名称
    ,base.first_nodal_time   as plan_collect_time           --规划到达集散时间
    ,base.first_nodal_code   as plan_collect_code           --规划到达集散编码
    ,base.first_nodal_name   as plan_collect_name           --规划到达集散名称
    ,base.first_nodal_time   as actual_collect_time         --实际交件集散点时间
    ,coalesce(branch.arrival_nodal_arrival_time
             ,branch.send_nodal_arrival_time
             ,base.first_nodal_time
    ) as actual_collect_arr_time    --实际交件集散点时间（包含支线任务到达时间）兜底逻辑到件、发件下一站、卸车、装车下一站
    ,base.first_nodal_time  as first_nodal_time           --始发集散点时间
    ,base.first_center_time as first_center_time          --始发中心时间
    ,coalesce(branch.arrival_center_shipment_no
             ,branch.send_center_shipment_no
     ) as actual_center_shipment_no
    ,base.first_nodal_send_time    as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,base.first_nodal_arrival_time as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,base.first_center_send_time   as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,nvl(base.first_center_arrival_time,base.first_center_send_time) as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,coalesce(branch.arrival_center_shipment_shifts
             ,branch.send_center_shipment_shifts
     ) as actual_center_shipment_shifts --实际交件支线任务班次
    ,case when base.first_center_arrival_time < nvl(base.first_center_send_time,'2099-99-99') then base.first_center_arrival_code
          else base.first_center_send_code end as first_center_send_code  --始发转运中心发件编码
    ,case when base.first_center_arrival_time < nvl(base.first_center_send_time,'2099-99-99') then base.first_center_arrival_name
          else base.first_center_send_name end as first_center_send_name  --始发转运中心发件名称
    ,bagging.scan_time  as first_nodal_bagging_time  -- 始发集散建包扫描时间
from (
    select
         a.waybill_no
        ,a.actual_send_time
        ,a.first_network_code
        ,a.first_nodal_time
        ,a.first_nodal_code
        ,a.first_nodal_name
        ,a.first_center_time
        ,a.first_center_code
        ,a.first_center_name
        ,b.first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
        ,b.first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
        ,b.first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
        ,b.first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
        ,b.first_center_send_code
        ,b.first_center_send_name
        ,b.first_center_arrival_code
        ,b.first_center_arrival_name
    from (
        select
             waybill_no
            ,max(actual_send_time  ) as actual_send_time
            ,max(first_network_code) as first_network_code
            ,max(first_nodal_time  ) as first_nodal_time
            ,max(first_nodal_code  ) as first_nodal_code
            ,max(first_nodal_name  ) as first_nodal_name
            ,max(first_center_time ) as first_center_time
            ,max(first_center_code ) as first_center_code
            ,max(first_center_name ) as first_center_name
        from (
            select
                  waybill_no
                 ,case when network_type = 6 and scan_type = 'send' then scan_time      end as actual_send_time       --网点实际发件时间/最早发件时间
                 ,case when network_type = 6 and scan_type = 'send' then scan_site_code end as first_network_code     --始发网点编码
                 ,case when network_type = 5  then scan_time      end as first_nodal_time  --实际交件集散点时间
                 ,case when network_type = 5  then scan_site_code end as first_nodal_code  --实际交件集散点编码
                 ,case when network_type = 5  then scan_site_name end as first_nodal_name  --实际交件集散点名称
                 ,case when network_type = 4  then scan_time      end as first_center_time --实际交件中心扫描时间
                 ,case when network_type = 4  then scan_site_code end as first_center_code --实际交件中心编码
                 ,case when network_type = 4  then scan_site_name end as first_center_name --实际交件中心名称
            from (
                 select
                     *,row_number() over(partition by waybill_no,network_type order by scan_time) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt_T2 base
            ) a where a.rn = 1
        ) a group by waybill_no
    ) a
    left join (
        select
             waybill_no
            ,max(first_nodal_send_time    ) as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
            ,max(first_nodal_arrival_time ) as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
            ,max(first_center_send_time   ) as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
            ,max(first_center_arrival_time) as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
            -- ,max(first_center_send_time   ) as first_center_send_time
            ,max(first_center_send_code   ) as first_center_send_code
            ,max(first_center_send_name   ) as first_center_send_name
            -- ,max(first_center_arrival_time) as first_center_arrival_time
            ,max(first_center_arrival_code) as first_center_arrival_code
            ,max(first_center_arrival_name) as first_center_arrival_name
        from(
            select
                 waybill_no
                ,case when network_type = 5 and scan_type = 'send'    then scan_time end as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
                ,case when network_type = 5 and scan_type = 'arrival' then scan_time end as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
                ,case when network_type = 4 and scan_type = 'arrival' then scan_time end as first_center_arrival_time --始发转运中心到件扫描时间  (20221113新增)
                ,first_center_send_time --始发转运中心发件扫描时间 (20221113新增)
                -- ,case when network_type = 4 and scan_type = 'send'    then scan_time      end as first_center_send_time        --始发转运中心发件扫描时间
                ,case when network_type = 4 and scan_type = 'send'    then scan_site_code end as first_center_send_code        --始发转运中心编码
                ,case when network_type = 4 and scan_type = 'send'    then scan_site_name end as first_center_send_name        --始发转运中心名称
                -- ,case when network_type = 4 and scan_type = 'arrival' then scan_time      end as first_center_arrival_time     --始发转运中心到件扫描时间
                ,case when network_type = 4 and scan_type = 'arrival' then scan_site_code end as first_center_arrival_code     --始发转运中心编码
                ,case when network_type = 4 and scan_type = 'arrival' then scan_site_name end as first_center_arrival_name     --始发转运中心名称
            from jms_tmp.dwd_tab_barscan_base_dt_T2
        ) a group by waybill_no
    ) b on a.waybill_no = b.waybill_no
) base
left join (
    select
         waybill_no as waybill_no              --运单号
        ,max(arrival_nodal_arrival_time    ) as arrival_nodal_arrival_time     --优先取到件扫描对应支线实际到达时间
        ,max(send_nodal_arrival_time       ) as send_nodal_arrival_time        --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_arrival_time   ) as arrival_center_arrival_time    --优先取到件扫描对应支线实际到达时间
        ,max(send_center_arrival_time      ) as send_center_arrival_time       --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_shipment_no    ) as arrival_center_shipment_no     --优先取到件扫描对应支线任务号
        ,max(send_center_shipment_no       ) as send_center_shipment_no        --次取发件扫描对应支线任务号
        ,max(arrival_center_shipment_shifts) as arrival_center_shipment_shifts --优先取到件扫描对应支线任务号
        ,max(send_center_shipment_shifts   ) as send_center_shipment_shifts    --次取发件扫描对应支线任务号
    from (
         select
               base.waybill_no     --运单号
              ,base.scan_time      --扫描时间
              ,base.input_time     --录入时间
              ,base.scan_site_code --扫描网点编码
              ,base.scan_site_name --扫描网点名称
              ,base.scan_type      --扫描类型
              ,case when base.network_type      = 5 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_nodal_arrival_time  --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 5 and base.scan_type = 'send'    then stop.actual_arrival_time end as send_nodal_arrival_time     --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_center_arrival_time --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.actual_arrival_time end as send_center_arrival_time    --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_no end as arrival_center_shipment_no          --优先取到件扫描对应支线任务号
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_no end as send_center_shipment_no             --次取发件扫描对应支线任务号
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_shifts end as arrival_center_shipment_shifts  --优先取到件扫描对应支线任务班次
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_shifts end as send_center_shipment_shifts     --次取发件扫描对应支线任务班次
         from (
             select
                  waybill_no
                 ,scan_time
                 ,input_time
                 ,scan_site_code
                 ,scan_site_name
                 ,next_station_code
                 ,transfer_code
                 ,network_type
                 ,next_network_type
                 ,scan_type
                 ,max(center_code) over(partition by waybill_no) as next_pre_site_code
             from (
                 select
                      *
                     ,case when network_type =  4 then scan_site_code end as center_code
                     ,row_number() over(partition by waybill_no,network_type order by scan_time) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt_T2 base
                 where scan_type = 'arrival'
                 union all
                 select
                      *
                     ,case when network_type =  4 then scan_site_code end as center_code
                     ,row_number() over(partition by waybill_no,next_network_type order by scan_time desc) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt_T2 base
                 where scan_type = 'send'
                 and !(scan_type = 'send' and network_type = 4)
             ) a where a.rn = 1
         ) base
         left join (
             select
                  ship_stop.shipment_no
                 ,ship_stop.sort_num
                 ,ship_stop.network_code        --经停网点编码
                 ,ship_stop.network_name        --经停网点名称
                 ,ship_stop.actual_arrival_time --最晚到车时间
                 ,ship_stop.pre_network_code    --经停网点编码
                 ,ship_stop.pre_network_name    --经停网点名称
                 ,ship.shipment_shifts          --任务班次 20230420新增
             from (
                 select
                      a.shipment_no         as shipment_no
                     ,a.sort_num            as sort_num
                     ,a.network_code        as network_code        --经停网点编码
                     ,a.network_name        as network_name        --经停网点名称
                     ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                     ,b.network_code        as pre_network_code    --经停网点编码
                     ,b.network_name        as pre_network_name    --经停网点名称
                 from (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                         where dt between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_hour }}'
                           and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                         -- where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                         --   and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) a
                 left join (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                         where dt between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_hour }}'
                           and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                         -- where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                         --   and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) b on a.shipment_no = b.shipment_no
                   and a.sort_num -1 = b.sort_num
                 union all
                 select
                      a.shipment_no         as shipment_no
                     ,a.sort_num            as sort_num
                     ,a.network_code        as network_code        --经停网点编码
                     ,a.network_name        as network_name        --经停网点名称
                     ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                     ,b.network_code        as pre_network_code    --经停网点编码
                     ,b.network_name        as pre_network_name    --经停网点名称
                 from (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_tms_shipment_stop_hf  --支线经停表
                         where dt = '{{ execution_date | cst_hour }}'
                           and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                         -- where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                         --   and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) a
                 left join (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_tms_shipment_stop_hf  --支线经停表
                         where dt = '{{ execution_date | cst_hour }}'
                           and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                         -- where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                         --   and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) b on a.shipment_no = b.shipment_no
                   and a.sort_num -1 = b.sort_num
             ) ship_stop
             left join (
                 select
                      shipment_no
                     ,shipment_shifts
                 from (
                     select
                          shipment_no
                         ,concat(shifts,'班') as shipment_shifts
                         ,row_number() over(partition by shipment_no order by update_time desc) as rn
                     from jms_ods.yl_tmsnew_branch_shipment_hi
                     where dt between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_hour }}'
                       and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                     -- where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                     --   and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                 ) a where a.rn = 1
                 union all
                 select
                      shipment_no
                     ,concat(shifts,'班') as shipment_shifts
                 from jms_ods.yl_tmsnew_tms_shipment_hf
                 where dt = '{{ execution_date | cst_hour }}'
                   and to_date(create_time) between date_add('{{ execution_date| cst_ds }}', -21) and '{{ execution_date | cst_ds }}'
                 -- where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                 --  and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
             ) ship on ship_stop.shipment_no = ship.shipment_no
        ) stop on stop.shipment_no = nvl(base.transfer_code,base.waybill_no)     --优先取到件任务号的最晚到车时间
              and stop.network_code = case when base.scan_type in ('send') and base.next_network_type <> 4 then base.next_station_code --发件，如果不是发往中心就正常用下一站取
                                           when base.scan_type in ('send') then base.next_pre_site_code --发件下一站，实际到件中心
                                           when base.scan_type in ('arrival') and stop.pre_network_code = base.next_station_code then base.scan_site_code end --到件，任务号的上一站，与实际上一站一致才取到件任务号
    ) a group by waybill_no
) branch on base.waybill_no = branch.waybill_no
left join(
    select
        waybill_no
        ,scan_site_code
        ,scan_time
        ,row_number() over (partition by waybill_no, scan_site_code order by scan_time ) as rn
    from jms_dwd.dwd_tab_barscan_bagging_base_hi
    where dt between date_add('{{ execution_date | cst_ds }}', -1)||'-00' and '{{ execution_date | cst_hour }}'
) bagging
    on base.waybill_no = bagging.waybill_no
        and base.first_nodal_code = bagging.scan_site_code
        and bagging.rn = 1
distribute by pmod(hash(rand()),60)
;

